CREATE OR REPLACE PACKAGE PKG_SEARCHPARTY IS

  -- Auteur  : RLEBRET
  -- Créé le : 04/02/2013 16:30:31
  -- Objectif : Centraliser les requêtes utilisées par SearchParty

  -- Déclaration des types publics
  /**
  * T_whereclause est un TYPE contenant la liste des clause wherre d'une requette
  * Ce Type est utilisée pa la fonction PKG_SEARCHPARTY.FCT_WHERECLAUSE
  */
  TYPE T_whereclause IS TABLE OF VARCHAR2(500);

  /**
  * TC_SEARCHPARTY_IN est un TYPE contenant la liste des paramètres 
  * en entrée de la recherhce SEARCHPARTY
  */
  TYPE T_SEARCHPARTY_IN IS RECORD(
    cityName          CMX_ORS.C_ADDRESS.CITY_NAME%TYPE, --VARCHAR2(100)
    streetName        CMX_ORS.C_ADDRESS.STREET_NAME%TYPE, --VARCHAR2(100)
    postalCode        CMX_ORS.C_ADDRESS.POSTAL_CD%TYPE, --VARCHAR2(100 BYTE)
    countryCode       CMX_ORS.C_ADDRESS.COUNTRY_CODE%TYPE, --VARCHAR2(100 BYTE)
    contractNumber    CMX_ORS.C_CONTRACT.NUMERO_CONTRAT%TYPE, --CHAR(50)
    contractOffice    CMX_ORS.C_CONTRACT.CONTRACT_OFFICE%TYPE, --VARCHAR2(50) 
    firstName         CMX_ORS.C_PARTY.FIRST_NAME_SEARCH%TYPE, --VARCHAR2(50) 
    lastName          CMX_ORS.C_PARTY.LAST_NAME_SEARCH%TYPE, --VARCHAR2(384)
    gender            CMX_ORS.C_PARTY.GENDER_CD%TYPE, --VARCHAR2(10) 
    partyType         CMX_ORS.C_PARTY.PARTY_TYPE%TYPE, --CHAR(14) 
    legalForm         CMX_ORS.C_PARTY.LEGAL_FORM_CD%TYPE, --CHAR(14)
    refClient         CMX_ORS.C_PARTY.DEBRANCHEMENT_SIGEO%TYPE, --VARCHAR2(20 BYTE)
    mdmId             CMX_ORS.C_PARTY.ROWID_OBJECT%TYPE, --CHAR(14)
    crmId             CMX_ORS.C_PARTY_XREF.PKEY_SRC_OBJECT%TYPE, --CHAR(14)
    phoneNumber       CMX_ORS.C_PARTY_PHONE.PHONE_NUMBER%TYPE, -- CHAR(13)
    electronicAddress CMX_ORS.C_PARTY_ELECT_AD.ELECTRONIC_ADDRESS%TYPE, --VARCHAR2(100) 
    recordsToReturn   NUMBER := 10);

  /**
  * TC_SEARCHPARTY_OUT est un TYPE définissant les colonnes retournée par la requete 
  * du SYS_REFCURSOR renvoyé par PRC_SEARCH
  */
  TYPE TC_SEARCHPARTY_OUT IS RECORD(
    rowidObject              CMX_ORS.C_PARTY.ROWID_OBJECT%TYPE,
    partyType                CMX_ORS.C_PARTY.PARTY_TYPE%TYPE,
    partyTypeDisp            CMX_ORS.C_LU_PARTY_TYPE.PARTY_TYPE_DISP%TYPE,
    genderCd                 CMX_ORS.C_PARTY.GENDER_CD%TYPE,
    genderDisp               CMX_ORS. C_LU_GENDER.GENDER_DISP%TYPE,
    legalFormCd              CMX_ORS.C_PARTY.LEGAL_FORM_CD%TYPE,
    libelleLegalForm         CMX_ORS.C_LU_PARTY_LEGAL_FORM.LIBELLE_LEGAL_FORM%TYPE,
    namePrefixCd             CMX_ORS.C_PARTY.NAME_PREFIX_CD%TYPE,
    lastName                 CMX_ORS.C_PARTY.LAST_NAME%TYPE,
    firstName                CMX_ORS.C_PARTY.FIRST_NAME%TYPE,
    birthdate                CMX_ORS.C_PERSON_DETAILS.BIRTHDATE%TYPE,
    unitNumber               CMX_ORS.C_ADDRESS.UNIT_NUMBER%TYPE,
    floorNumber              CMX_ORS.C_ADDRESS.FLOOR_NUMBER%TYPE,
    streetNumber             CMX_ORS.C_ADDRESS.STREET_NUMBER%TYPE,
    streetType               CMX_ORS.C_ADDRESS.STREET_TYPE%TYPE,
    streetName               CMX_ORS.C_ADDRESS.STREET_NAME%TYPE,
    addressLine1             CMX_ORS.C_ADDRESS.ADDRESS_LINE_1%TYPE,
    addressLine2             CMX_ORS.C_ADDRESS.ADDRESS_LINE_2%TYPE,
    addressLine3             CMX_ORS.C_ADDRESS.ADDRESS_LINE_3%TYPE,
    addressLine5             CMX_ORS.C_ADDRESS.ADDRESS_LINE_5%TYPE,
    postalCd                 CMX_ORS.C_ADDRESS.POSTAL_CD%TYPE,
    cityName                 CMX_ORS.C_ADDRESS.CITY_NAME%TYPE,
    countryName              CMX_ORS.C_ADDRESS.COUNTRY_NAME%TYPE,
    countryCode              CMX_ORS.C_ADDRESS.COUNTRY_CODE%TYPE,
    hexacleVoie              CMX_ORS.C_ADDRESS.HEXACLE_VOIE%TYPE,
    hexacleLocalite          CMX_ORS.C_ADDRESS.HEXACLE_LOCALITE%TYPE,
    homePhoneNumber          CMX_ORS.C_PARTY_PHONE.PHONE_NUMBER%TYPE,
    cellPhoneNumber          CMX_ORS.C_PARTY_PHONE.PHONE_NUMBER%TYPE,
    workPhoneNumber          CMX_ORS.C_PARTY_PHONE.PHONE_NUMBER%TYPE,
    faxPhoneNumber           CMX_ORS.C_PARTY_PHONE.PHONE_NUMBER%TYPE,
    electronicAddress        CMX_ORS.C_PARTY_ELECT_AD.ELECTRONIC_ADDRESS%TYPE,
    typeContrat              CMX_ORS.c_Contract.TYPE_CONTRAT%TYPE,
    statutContrat            CMX_ORS.c_Contract.STATUT%TYPE,
    numeroContrat            CMX_ORS.c_Contract.NUMERO_CONTRAT%TYPE,
    contractOffice           CMX_ORS.c_Contract.CONTRACT_OFFICE%TYPE,
    dateDebutValiditeContrat CMX_ORS.c_Contract.DATE_DEBUT_VALIDITE%TYPE,
    dateFinValiditeContrat   CMX_ORS.c_Contract.DATE_FIN_VALIDITE%TYPE,
    flagVip                  CMX_ORS.C_PARTY.FLAG_VIP%TYPE,
    flagPartenaire           CMX_ORS.C_PARTY.FLAG_PARTENAIRE%TYPE,
    debranchementSigeo       CMX_ORS.C_PARTY.DEBRANCHEMENT_SIGEO%TYPE,
    crmId                    CMX_ORS.C_PARTY_XREF.PKEY_SRC_OBJECT%TYPE,
    logementId               CMX_ORS.C_PARTY_XREF.PKEY_SRC_OBJECT%TYPE,
    serviceId                CMX_ORS.C_PARTY_XREF.PKEY_SRC_OBJECT%TYPE);

  -- Déclaration des fonctions et procédures publiques
  /**
  *    PRC_SEARCH EST LA PROCEDURE PRINCIPALE 
  *    permettant d'effectuer une recherche dans mdm à partir des paramètres p_t_parametre.
  *    le resultat est un SYS_REFCURSOR renvoyé dans la variable p_c_recherche
  */
  PROCEDURE PRC_SEARCH(p_t_parametre IN T_SEARCHPARTY_IN, p_c_recherche OUT SYS_REFCURSOR);

  /**
  *    PRC_SEARCH EST LA PROCEDURE PRINCIPALE 
  *    permettant d'effectuer une recherche dans mdm à partir des paramètres p_t_parametre.
  *    le resultat est un SYS_REFCURSOR renvoyé dans la variable p_c_recherche
  */
  PROCEDURE PRC_SEARCH(p_c_recherche         OUT SYS_REFCURSOR,
                       p_h_cityName          IN CMX_ORS.C_ADDRESS.CITY_NAME%TYPE DEFAULT NULL, --VARCHAR2(100)
                       p_h_streetName        IN CMX_ORS.C_ADDRESS.STREET_NAME%TYPE DEFAULT NULL, --VARCHAR2(100)
                       p_h_postalCode        IN CMX_ORS.C_ADDRESS.POSTAL_CD%TYPE DEFAULT NULL, --VARCHAR2(100 BYTE)
                       p_h_countryCode       IN CMX_ORS.C_ADDRESS.COUNTRY_CODE%TYPE DEFAULT NULL, --VARCHAR2(100 BYTE)
                       p_h_contractNumber    IN CMX_ORS.C_CONTRACT.NUMERO_CONTRAT%TYPE DEFAULT NULL, --CHAR(50)
                       p_h_contractOffice    IN CMX_ORS.C_CONTRACT.CONTRACT_OFFICE%TYPE DEFAULT NULL, --VARCHAR2(50) 
                       p_h_firstName         IN CMX_ORS.C_PARTY.FIRST_NAME_SEARCH%TYPE DEFAULT NULL, --VARCHAR2(50) 
                       p_h_lastName          IN CMX_ORS.C_PARTY.LAST_NAME_SEARCH%TYPE DEFAULT NULL, --VARCHAR2(384)
                       p_h_gender            IN CMX_ORS.C_PARTY.GENDER_CD%TYPE DEFAULT NULL, --VARCHAR2(10) 
                       p_h_partyType         IN CMX_ORS.C_PARTY.PARTY_TYPE%TYPE DEFAULT NULL, --CHAR(14) 
                       p_h_legalForm         IN CMX_ORS.C_PARTY.LEGAL_FORM_CD%TYPE DEFAULT NULL, --CHAR(14)
                       p_h_refClient         IN CMX_ORS.C_PARTY.DEBRANCHEMENT_SIGEO%TYPE DEFAULT NULL, --VARCHAR2(20 BYTE)
                       p_h_mdmId             IN CMX_ORS.C_PARTY.ROWID_OBJECT%TYPE DEFAULT NULL, --CHAR(14)
                       p_h_crmId             CMX_ORS.C_PARTY_XREF.PKEY_SRC_OBJECT%TYPE, --VARCHAR2(255) 
                       p_h_phoneNumber       IN CMX_ORS.C_PARTY_PHONE.PHONE_NUMBER%TYPE DEFAULT NULL, -- CHAR(13)
                       p_h_electronicAddress IN CMX_ORS.C_PARTY_ELECT_AD.ELECTRONIC_ADDRESS%TYPE DEFAULT NULL, --VARCHAR2(100) 
                       p_n_recordsToReturn   IN NUMBER DEFAULT 1);

  /**
  *    PRC_SEARCH_IN_ALL est une procedure qui retourne le SYS_REFCURSOR final après
  *    le pré-filtre effectué par les autres fonction PRC_SEARCH_IN_xxx
  *    En fait, le SYS_REFCURSOR s'appuie sur la table temporaire T_ROWID_OBJECT alimentée
  *    par les fonctions PRC_SEARCH_IN_xxx
  */
  PROCEDURE PRC_SEARCH_IN_ALL(p_c_recherche OUT SYS_REFCURSOR);

  /**
  *    PRC_SEARCH_IN_C_ADDRESS permet de préfiltrer les résultats en utilisants
  *    les critères de recherche sur l'adresse
  *    le résultats du pré-filtre est une liste de C_PARTY.ROWID_OBJECT 
  *    dans la table temporaire T_ROWID_OBJECT
  */
  PROCEDURE PRC_SEARCH_IN_C_ADDRESS(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB);

  /**
  *    PRC_SEARCH_IN_C_CONTRACT permet de préfiltrer les résultats en utilisants
  *    les critères de recherche sur les contrats
  *    le résultats du pré-filtre est une liste de C_PARTY.ROWID_OBJECT 
  *    dans la table temporaire T_ROWID_OBJECT
  */
  PROCEDURE PRC_SEARCH_IN_C_CONTRACT(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB);

  /**
  *    PRC_SEARCH_IN_C_PARTY permet de préfiltrer les résultats en utilisants
  *    les critères de recherche sur les individus
  *    le résultats du pré-filtre est une liste de C_PARTY.ROWID_OBJECT 
  *    dans la table temporaire T_ROWID_OBJECT
  */
  PROCEDURE PRC_SEARCH_IN_C_PARTY(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB);

  /**
  *    PRC_SEARCH_IN_C_PARTY_ELECT_AD permet de préfiltrer les résultats en utilisants
  *    les critères de recherche sur l'adresse email
  *    le résultats du pré-filtre est une liste de C_PARTY.ROWID_OBJECT 
  *    dans la table temporaire T_ROWID_OBJECT
  */
  PROCEDURE PRC_SEARCH_IN_C_PARTY_ELECT_AD(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB);

  /**
  *    PRC_SEARCH_IN_C_PARTY_PHONE permet de préfiltrer les résultats en utilisants
  *    les critères de recherche sur le numéro de telephone (dom, fax, bur, por)
  *    le résultats du pré-filtre est une liste de C_PARTY.ROWID_OBJECT 
  *    dans la table temporaire T_ROWID_OBJECT
  */
  PROCEDURE PRC_SEARCH_IN_C_PARTY_PHONE(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB);

  /**
  *    PRC_SEARCH_IN_C_PARTYXREF permet de préfiltrer les résultats en utilisants
  *    les critères de recherche sur l'id crm
  */
  PROCEDURE PRC_SEARCH_IN_C_PARTYXREF(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB);

  /**
  *    FCT_UPPER_TRANSLATE permet de remplacer les caractères accentuée et de mettre en majuscule
  */
  FUNCTION FCT_UPPER_TRANSLATE(p_v_TEXT IN VARCHAR2) RETURN VARCHAR2;

  /**
  *    FCT_WHERECLAUSE permet de retourner la where clause pour un select à partir d'un tableau passé en parametre
  *    ATTENTION: le mot clé 'WHERE' n'est pas présent dans le résultat
  *    tous les critères sont concaténés avec la clause AND
  */
  FUNCTION FCT_WHERECLAUSE(p_t_whereclause IN T_whereclause) RETURN CLOB;

END PKG_SEARCHPARTY;
/
CREATE OR REPLACE PACKAGE BODY PKG_SEARCHPARTY IS

  -- Déclaration des types privés

  -- Déclaration des constantes privées
  c_i_recordsToReturn CONSTANT VARCHAR(8) := '999';

  -- Déclaration des variables privées

  /**
  *    PRC_SEARCH EST LA PROCEDURE PRINCIPALE 
  *    permettant d'effectuer une recherche dans mdm à partir des paramètres p_t_parametre.
  *    le resultat est un SYS_REFCURSOR renvoyé dans la variable p_c_recherche
  */
  PROCEDURE PRC_SEARCH(p_t_parametre IN T_SEARCHPARTY_IN, p_c_recherche OUT SYS_REFCURSOR) IS
    l_v_req       CLOB;
    l_v_req2      CLOB;
    l_i_cursor    BINARY_INTEGER;
    l_i_dummy     INTEGER;
    l_t_parametre T_SEARCHPARTY_IN;
  BEGIN
  
    l_t_parametre := p_t_parametre;
    IF (TRIM(l_t_parametre.recordsToReturn) IS NULL)
    THEN
      l_t_parametre.recordsToReturn := c_I_recordsToReturn;
    END IF;
  
    -- Pré-filtre en fontion des critères individu
    PKG_SEARCHPARTY.PRC_SEARCH_IN_C_PARTY(l_t_parametre, l_v_req);
  
    -- Pré-filtre en fontion du téléphone
    PKG_SEARCHPARTY.PRC_SEARCH_IN_C_PARTY_PHONE(l_t_parametre, l_v_req);
  
    -- Pré-filtre en fontion de l'adresse mail
    PKG_SEARCHPARTY.PRC_SEARCH_IN_C_PARTY_ELECT_AD(l_t_parametre, l_v_req);
  
    -- Pré-filtre en fontion de l'id crm
    PKG_SEARCHPARTY.PRC_SEARCH_IN_C_PARTYXREF(l_t_parametre, l_v_req);
  
    -- Pré-filtre en fontion des contrats
    PKG_SEARCHPARTY.PRC_SEARCH_IN_C_CONTRACT(l_t_parametre, l_v_req);
  
    -- Pré-filtre en fontion des critères de l'adresse
    PKG_SEARCHPARTY.PRC_SEARCH_IN_C_ADDRESS(l_t_parametre, l_v_req);
  
    IF (l_v_req IS NOT NULL)
    THEN
      l_v_req2 := 'INSERT INTO T_ROWID_TEMP';
      l_v_req2 := l_v_req2 || chr(13) || 'SELECT rowid_object';
      l_v_req2 := l_v_req2 || chr(13) || '  FROM (';
      l_v_req2 := l_v_req2 || chr(13) ||
                  '       SELECT C_PARTY.rowid_object, C_PARTY.last_name, C_PARTY.first_name, rownum rnum';
      l_v_req2 := l_v_req2 || chr(13) || '         FROM C_PARTY, (';
      l_v_req2 := l_v_req2 || chr(13) || l_v_req;
      l_v_req2 := l_v_req2 || chr(13) || '        ) t';
      l_v_req2 := l_v_req2 || chr(13) || '        WHERE t.rowid_object=C_PARTY.rowid_object';
      l_v_req2 := l_v_req2 || chr(13) ||
                  '        order by C_PARTY.last_name, C_PARTY.first_name, C_PARTY.rowid_object';
      l_v_req2 := l_v_req2 || chr(13) || ')';
      l_v_req2 := l_v_req2 || chr(13) || ' WHERE rnum <= ' || l_t_parametre.recordsToReturn;
    
      /**
      * on alimente la table tempo T_ROWID_OBJECT avec le select construit précédemment
      */
      dbms_output.put_line('---------------------------------------------------');
      dbms_output.put_line(l_v_req2);
      dbms_output.put_line('---------------------------------------------------');
      l_i_cursor := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(l_i_cursor, l_v_req2, DBMS_SQL.NATIVE);
      l_i_dummy := DBMS_SQL.EXECUTE(l_i_cursor);
      DBMS_SQL.CLOSE_CURSOR(l_i_cursor);
      --EXECUTE IMMEDIATE l_v_req2;
      dbms_output.put_line('nb de rowid_object insérés:' || l_i_dummy);
      dbms_output.put_line('---------------------------------------------------');
    END IF;
    -- ouverture du SYS_REFCURSOR a retourner en paramètre OUT
    PKG_SEARCHPARTY.PRC_SEARCH_IN_ALL(p_c_recherche);
  END;

  /**
  *    PRC_SEARCH EST LA PROCEDURE PRINCIPALE 
  *    permettant d'effectuer une recherche dans mdm à partir des paramètres p_t_parametre.
  *    le resultat est un SYS_REFCURSOR renvoyé dans la variable p_c_recherche
  */
  PROCEDURE PRC_SEARCH(p_c_recherche         OUT SYS_REFCURSOR,
                       p_h_cityName          IN CMX_ORS.C_ADDRESS.CITY_NAME%TYPE DEFAULT NULL, --VARCHAR2(100)
                       p_h_streetName        IN CMX_ORS.C_ADDRESS.STREET_NAME%TYPE DEFAULT NULL, --VARCHAR2(100)
                       p_h_postalCode        IN CMX_ORS.C_ADDRESS.POSTAL_CD%TYPE DEFAULT NULL, --VARCHAR2(100 BYTE)
                       p_h_countryCode       IN CMX_ORS.C_ADDRESS.COUNTRY_CODE%TYPE DEFAULT NULL, --VARCHAR2(100 BYTE)
                       p_h_contractNumber    IN CMX_ORS.C_CONTRACT.NUMERO_CONTRAT%TYPE DEFAULT NULL, --CHAR(50)
                       p_h_contractOffice    IN CMX_ORS.C_CONTRACT.CONTRACT_OFFICE%TYPE DEFAULT NULL, --VARCHAR2(50) 
                       p_h_firstName         IN CMX_ORS.C_PARTY.FIRST_NAME_SEARCH%TYPE DEFAULT NULL, --VARCHAR2(50) 
                       p_h_lastName          IN CMX_ORS.C_PARTY.LAST_NAME_SEARCH%TYPE DEFAULT NULL, --VARCHAR2(384)
                       p_h_gender            IN CMX_ORS.C_PARTY.GENDER_CD%TYPE DEFAULT NULL, --VARCHAR2(10) 
                       p_h_partyType         IN CMX_ORS.C_PARTY.PARTY_TYPE%TYPE DEFAULT NULL, --CHAR(14) 
                       p_h_legalForm         IN CMX_ORS.C_PARTY.LEGAL_FORM_CD%TYPE DEFAULT NULL, --CHAR(14)
                       p_h_refClient         IN CMX_ORS.C_PARTY.DEBRANCHEMENT_SIGEO%TYPE DEFAULT NULL, --VARCHAR2(20 BYTE)
                       p_h_mdmId             IN CMX_ORS.C_PARTY.ROWID_OBJECT%TYPE DEFAULT NULL, --CHAR(14)
                       p_h_crmId             CMX_ORS.C_PARTY_XREF.PKEY_SRC_OBJECT%TYPE, --VARCHAR2(255)
                       p_h_phoneNumber       IN CMX_ORS.C_PARTY_PHONE.PHONE_NUMBER%TYPE DEFAULT NULL, -- CHAR(13)
                       p_h_electronicAddress IN CMX_ORS.C_PARTY_ELECT_AD.ELECTRONIC_ADDRESS%TYPE DEFAULT NULL, --VARCHAR2(100) 
                       p_n_recordsToReturn   IN NUMBER DEFAULT 1) IS
    l_t_param PKG_SEARCHPARTY.T_SEARCHPARTY_IN;
  BEGIN
    l_t_param.lastName          := p_h_lastName;
    l_t_param.firstName         := p_h_firstName;
    l_t_param.gender            := p_h_gender;
    l_t_param.partyType         := p_h_partyType;
    l_t_param.legalForm         := p_h_legalForm;
    l_t_param.refClient         := p_h_refClient;
    l_t_param.mdmId             := p_h_mdmId;
    l_t_param.crmId             := p_h_crmId;
    l_t_param.phoneNumber       := p_h_phoneNumber;
    l_t_param.electronicAddress := p_h_electronicAddress;
    l_t_param.countryCode       := p_h_countryCode;
    l_t_param.postalCode        := p_h_postalCode;
    l_t_param.cityName          := p_h_cityName;
    l_t_param.streetName        := p_h_streetName;
    l_t_param.contractNumber    := p_h_contractNumber;
    l_t_param.contractOffice    := p_h_contractOffice;
    l_t_param.recordsToReturn   := p_n_recordsToReturn;
  
    PKG_SEARCHPARTY.PRC_SEARCH(l_t_param, p_c_recherche);
  END;

  /**
  *    PRC_SEARCH_IN_ALL est une procedure qui retourne le SYS_REFCURSOR final après
  *    le pré-filtre effectué par les autres fonction PRC_SEARCH_IN_xxx
  *    En fait, le SYS_REFCURSOR s'appuie sur la table temporaire T_ROWID_OBJECT alimentée
  *    par les fonctions PRC_SEARCH_IN_xxx
  */
  PROCEDURE PRC_SEARCH_IN_ALL(p_c_recherche OUT SYS_REFCURSOR) IS
    l_v_req    CLOB;
    l_v_req2   CLOB;
    l_i_cursor BINARY_INTEGER;
    l_i_dummy  INTEGER;
  BEGIN
    -- ouverture du SYS_REFCURSOR a retourner en paramètre OUT
    l_v_req := l_v_req || chr(13) || 'SELECT *';
    l_v_req := l_v_req || chr(13) || '    FROM (SELECT c_party.rowid_object rowidobject,';
    l_v_req := l_v_req || chr(13) || '                 c_party.party_type partyType,';
    l_v_req := l_v_req || chr(13) ||
               '                 c_lu_party_type.party_type_disp partyTypeDisp,';
    l_v_req := l_v_req || chr(13) || '                 c_party.gender_cd genderCd,';
    l_v_req := l_v_req || chr(13) || '                 c_lu_gender.gender_disp genderDisp,';
    l_v_req := l_v_req || chr(13) || '                 c_party.legal_form_cd legalFormCd,';
    l_v_req := l_v_req || chr(13) ||
               '                 c_lu_party_legal_form.libelle_legal_form libelleLegalForm,';
    l_v_req := l_v_req || chr(13) || '                 c_party.name_prefix_cd namePrefixCd,';
    l_v_req := l_v_req || chr(13) || '                 c_party.last_name lastName,';
    l_v_req := l_v_req || chr(13) || '                 c_party.first_name firstName,';
    l_v_req := l_v_req || chr(13) || '                 c_person_details.birthdate birthdate,';
    l_v_req := l_v_req || chr(13) ||
               '                 0 /*mv_pkg_ws_party_address_pp.unit_number*/ unitNumber,';
    l_v_req := l_v_req || chr(13) ||
               '                 0 /*mv_pkg_ws_party_address_pp.floor_number*/ floorNumber,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.street_number*/ streetNumber,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.street_type*/ streetType,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.street_name*/ streetName,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.address_line_1*/ addressLine1,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.address_line_2*/ addressLine2,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.address_line_3*/ addressLine3,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.address_line_5*/ addressLine5,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.postal_cd*/ postalCd,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.city_name*/ cityName,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.country_name*/ countryName,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.country_code*/ countryCode,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.hexacle_voie*/ hexacleVoie,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*mv_pkg_ws_party_address_pp.hexacle_localite*/ hexacleLocalite,';
    l_v_req := l_v_req || chr(13) ||
               '                 mv_pkg_ws_party_phone.dom AS homePhoneNumber,';
    l_v_req := l_v_req || chr(13) ||
               '                 mv_pkg_ws_party_phone.por AS cellPhoneNumber,';
    l_v_req := l_v_req || chr(13) ||
               '                 mv_pkg_ws_party_phone.bur AS workPhoneNumber,';
    l_v_req := l_v_req || chr(13) ||
               '                 mv_pkg_ws_party_phone.fax AS faxPhoneNumber,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*pkg_ws_party_e_address_pp.electronic_address*/ electronicAddress,';
    l_v_req := l_v_req || chr(13) || '                 '''' typeContrat,';
    l_v_req := l_v_req || chr(13) || '                 '''' statutContrat,';
    l_v_req := l_v_req || chr(13) || '                 '''' numeroContrat,';
    l_v_req := l_v_req || chr(13) || '                 '''' contractOffice,';
    l_v_req := l_v_req || chr(13) || '                 '''' dateDebutValiditeContrat,';
    l_v_req := l_v_req || chr(13) || '                 '''' dateFinValiditeContrat,';
    l_v_req := l_v_req || chr(13) || '                 c_party.flag_vip flagVip,';
    l_v_req := l_v_req || chr(13) || '                 c_party.flag_partenaire flagPartenaire,';
    l_v_req := l_v_req || chr(13) ||
               '                 c_party.debranchement_sigeo debranchementSigeo,';
    l_v_req := l_v_req || chr(13) ||
               '                 '''' /*c_party_crm_id.PKEY_SRC_OBJECT*/ crmId,';
    l_v_req := l_v_req || chr(13) || '                 '''' logementId,';
    l_v_req := l_v_req || chr(13) || '                 '''' serviceId';
    l_v_req := l_v_req || chr(13) || '            FROM T_ROWID_TEMP t';
  
    l_v_req2 := l_v_req2 || chr(13) || '           INNER JOIN CMX_ORS.c_party';
    l_v_req2 := l_v_req2 || chr(13) || '              ON c_party.rowid_object = t.rowid_object';
    l_v_req2 := l_v_req2 || chr(13) || '            LEFT OUTER JOIN CMX_ORS.c_lu_party_legal_form';
    l_v_req2 := l_v_req2 || chr(13) ||
                '              ON c_party.legal_form_cd = c_lu_party_legal_form.type_legal_form';
    l_v_req2 := l_v_req2 || chr(13) || '            LEFT OUTER JOIN CMX_ORS.c_lu_gender';
    l_v_req2 := l_v_req2 || chr(13) ||
                '              ON c_party.gender_cd = c_lu_gender.gender_code';
    l_v_req2 := l_v_req2 || chr(13) || '            LEFT OUTER JOIN CMX_ORS.c_lu_party_type';
    l_v_req2 := l_v_req2 || chr(13) ||
                '              ON c_party.party_type = c_lu_party_type.party_type';
    l_v_req2 := l_v_req2 || chr(13) ||
                '            LEFT OUTER JOIN (SELECT PARTY_ID, BUR, DOM, POR, FAX';
    l_v_req2 := l_v_req2 || chr(13) ||
                '                              FROM (SELECT PARTY_ID, PHONE_NUMBER, PHONE_TYPE';
    l_v_req2 := l_v_req2 || chr(13) ||
                '                                      FROM CMX_ORS.C_PARTY_PHONE) PIVOT(MAX(PHONE_NUMBER) FOR PHONE_TYPE IN(''BUR'' AS BUR,';
    l_v_req2 := l_v_req2 || chr(13) ||
                '                                                                                                            ''DOM'' AS DOM,';
    l_v_req2 := l_v_req2 || chr(13) ||
                '                                                                                                            ''POR'' AS POR,';
    l_v_req2 := l_v_req2 || chr(13) ||
                '                                                                                                            ''FAX'' AS FAX))) mv_pkg_ws_party_phone';
    l_v_req2 := l_v_req2 || chr(13) ||
                '              ON c_party.rowid_object = mv_pkg_ws_party_phone.party_id';
    l_v_req2 := l_v_req2 || chr(13) || '            LEFT OUTER JOIN CMX_ORS.c_person_details';
    l_v_req2 := l_v_req2 || chr(13) ||
                '              ON c_party.rowid_object = c_person_details.party_id';
    /*l_v_req2 := l_v_req2 || chr(13) ||
                '            LEFT OUTER JOIN (SELECT C_PARTY_XREF.ROWID_OBJECT,';
    l_v_req2 := l_v_req2 || chr(13) ||
                '                                    C_PARTY_XREF.PKEY_SRC_OBJECT';
    l_v_req2 := l_v_req2 || chr(13) || '                               FROM C_PARTY_XREF  ';
    l_v_req2 := l_v_req2 || chr(13) || '                              INNER JOIN C_REPOS_SYSTEM  ';
    l_v_req2 := l_v_req2 || chr(13) ||
                '                                 ON C_PARTY_XREF.ROWID_SYSTEM = C_REPOS_SYSTEM.ROWID_SYSTEM';
    l_v_req2 := l_v_req2 || chr(13) ||
                '                              WHERE C_REPOS_SYSTEM.SYSTEM_NAME = ''CRM'') c_party_crm_id';
    l_v_req2 := l_v_req2 || chr(13) ||
                '              ON c_party.rowid_object = c_party_crm_id.rowid_object';*/
    l_v_req2 := l_v_req2 || chr(13) ||
                '           ORDER BY c_party.last_name ASC, c_party.first_name ASC, c_party.rowid_object ASC)';
  
    dbms_output.put_line('---------------------------------------------------');
    dbms_output.put_line(l_v_req);
    dbms_output.put_line(l_v_req2);
    dbms_output.put_line('---------------------------------------------------');
  
    l_v_req    := l_v_req || l_v_req2;
    l_i_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(l_i_cursor, l_v_req, DBMS_SQL.NATIVE);
    l_i_dummy := DBMS_SQL.EXECUTE(l_i_cursor);
    dbms_output.put_line('nb de résultat:' || l_i_dummy);
    dbms_output.put_line('---------------------------------------------------');
  
    p_c_recherche := DBMS_SQL.to_refcursor(l_i_cursor);
  
  END;

  /**
  *    PRC_SEARCH_IN_C_ADDRESS permet de construire une requette pour
  *    préfiltrer les résultats en utilisants
  *    les critères de recherche sur l'adresse
  */
  PROCEDURE PRC_SEARCH_IN_C_ADDRESS(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB) IS
    l_t_whereclause_1   PKG_SEARCHPARTY.T_whereclause;
    l_t_whereclause_2   PKG_SEARCHPARTY.T_whereclause;
    l_v_city_name_req   CMX_ORS.C_ADDRESS.CITY_NAME%TYPE := PKG_SEARCHPARTY.FCT_UPPER_TRANSLATE(p_t_parametre.cityName);
    l_v_street_name_req CMX_ORS.C_ADDRESS.STREET_NAME%TYPE := PKG_SEARCHPARTY.FCT_UPPER_TRANSLATE(p_t_parametre.streetName);
  BEGIN
    l_t_whereclause_1 := PKG_SEARCHPARTY.T_whereclause(' ');
    l_t_whereclause_2 := PKG_SEARCHPARTY.T_whereclause(' ');
  
    -- si un critère d'adresse est passé en paramètre
    IF (TRIM(p_t_parametre.cityName) IS NOT NULL OR TRIM(p_t_parametre.streetName) IS NOT NULL OR
       TRIM(p_t_parametre.postalCode) IS NOT NULL OR TRIM(p_t_parametre.countryCode) IS NOT NULL)
    THEN
      l_t_whereclause_1.DELETE;
      l_t_whereclause_2.DELETE;
    
      -- CI-dessous, on aliment le tableau l_t_whereclause_1 et l_t_whereclause_2 permettant de 
      -- générer la clause where pour filtrer les adresses
      -- l_t_whereclause_1 stock les clauses pour les adresse françaises
      -- l_t_whereclause_1 stock les clauses pour les adresse étrangères
      IF (TRIM(p_t_parametre.cityName) IS NOT NULL)
      THEN
        l_t_whereclause_1.EXTEND;
        l_t_whereclause_1(l_t_whereclause_1.last) := 'CMX_ORS.c_address.city_name LIKE ''' ||
                                                     l_v_city_name_req || '%''';
        l_t_whereclause_2.EXTEND;
        l_t_whereclause_2(l_t_whereclause_2.last) := 'PKG_SEARCHPARTY.FCT_UPPER_TRANSLATE(CMX_ORS.c_address.city_name) LIKE ''' ||
                                                     l_v_city_name_req || '%''';
      END IF;
      IF (TRIM(p_t_parametre.streetName) IS NOT NULL)
      THEN
        l_t_whereclause_1.EXTEND;
        l_t_whereclause_1(l_t_whereclause_1.last) := 'CMX_ORS.c_address.street_name LIKE ''' ||
                                                     l_v_street_name_req || '%''';
        l_t_whereclause_2.EXTEND;
        l_t_whereclause_2(l_t_whereclause_2.last) := 'PKG_SEARCHPARTY.FCT_UPPER_TRANSLATE(CMX_ORS.c_address.street_name) LIKE ''' ||
                                                     l_v_street_name_req || '%''';
      END IF;
      IF (TRIM(p_t_parametre.postalCode) IS NOT NULL)
      THEN
        l_t_whereclause_1.EXTEND;
        l_t_whereclause_1(l_t_whereclause_1.last) := 'CMX_ORS.c_address.postal_cd LIKE ''' ||
                                                     p_t_parametre.postalCode || '%''';
        l_t_whereclause_2.EXTEND;
        l_t_whereclause_2(l_t_whereclause_2.last) := 'CMX_ORS.c_address.postal_cd LIKE ''' ||
                                                     p_t_parametre.postalCode || '%''';
      END IF;
      IF (TRIM(p_t_parametre.countryCode) IS NOT NULL)
      THEN
        l_t_whereclause_1.EXTEND;
        l_t_whereclause_1(l_t_whereclause_1.last) := 'CMX_ORS.c_address.COUNTRY_CODE = ''' ||
                                                     p_t_parametre.countryCode || '''';
        l_t_whereclause_2.EXTEND;
        l_t_whereclause_2(l_t_whereclause_2.last) := 'CMX_ORS.c_address.COUNTRY_CODE = ''' ||
                                                     p_t_parametre.countryCode || '''';
      END IF;
    
      -- si p_v_req contient déjà un select 
      -- alors on gère une intersection entre les requètes
      IF (p_v_req IS NOT NULL)
      THEN
        p_v_req := p_v_req || chr(13) || 'INTERSECT' || chr(13);
      END IF;
    
      p_v_req := p_v_req || '(SELECT CMX_ORS.C_PARTY_ADDRESS_REL.PARTY_ID AS rowid_object';
      p_v_req := p_v_req || chr(13) || 'FROM CMX_ORS.c_address, CMX_ORS.C_PARTY_ADDRESS_REL';
      p_v_req := p_v_req || chr(13) || 'where CMX_ORS.C_PARTY_ADDRESS_REL.ADDRESS_TYPE = ''PP''';
      p_v_req := p_v_req || chr(13) || 'and CMX_ORS.c_address.COUNTRY_CODE = ''FR''';
      p_v_req := p_v_req || chr(13) ||
                 'and CMX_ORS.C_ADDRESS.ROWID_OBJECT = CMX_ORS.C_PARTY_ADDRESS_REL.ADDRESS_ID';
      p_v_req := p_v_req || chr(13) || 'and ' || PKG_SEARCHPARTY.FCT_WHERECLAUSE(l_t_whereclause_1);
    
      p_v_req := p_v_req || chr(13) ||
                 'UNION SELECT CMX_ORS.C_PARTY_ADDRESS_REL.PARTY_ID AS rowid_object';
      p_v_req := p_v_req || chr(13) || 'FROM CMX_ORS.c_address, CMX_ORS.C_PARTY_ADDRESS_REL';
      p_v_req := p_v_req || chr(13) || 'where CMX_ORS.C_PARTY_ADDRESS_REL.ADDRESS_TYPE = ''PP''';
      p_v_req := p_v_req || chr(13) || 'and CMX_ORS.c_address.COUNTRY_CODE != ''FR''';
      p_v_req := p_v_req || chr(13) ||
                 'and CMX_ORS.C_ADDRESS.ROWID_OBJECT = CMX_ORS.C_PARTY_ADDRESS_REL.ADDRESS_ID';
      p_v_req := p_v_req || chr(13) || 'and ' || PKG_SEARCHPARTY.FCT_WHERECLAUSE(l_t_whereclause_2);
      p_v_req := p_v_req || chr(13) || ')';
    END IF;
  END;

  /**
  *    PRC_SEARCH_IN_C_CONTRACT permet de construire une requette pour
  *    préfiltrer les résultats en utilisants
  *    les critères de recherche sur les contrats
  */
  PROCEDURE PRC_SEARCH_IN_C_CONTRACT(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB) IS
    l_t_whereclause         PKG_SEARCHPARTY.T_whereclause;
    l_v_contract_office_req CMX_ORS.c_contract.CONTRACT_OFFICE%TYPE := PKG_SEARCHPARTY.FCT_UPPER_TRANSLATE(p_t_parametre.contractOffice);
  BEGIN
    l_t_whereclause := PKG_SEARCHPARTY.T_whereclause(' ');
  
    -- si un critère contrat est passé en paramètre
    IF (TRIM(p_t_parametre.contractOffice) IS NOT NULL OR
       TRIM(p_t_parametre.contractNumber) IS NOT NULL)
    THEN
      l_t_whereclause.DELETE;
    
      -- CI-dessous, on aliment le tableau l_t_whereclause permettant de 
      -- générer la clause where pour filtrer les adresses
      IF (TRIM(p_t_parametre.contractOffice) IS NOT NULL)
      THEN
        l_t_whereclause.EXTEND;
        l_t_whereclause(l_t_whereclause.last) := 'CMX_ORS.c_contract.contract_office LIKE ''' ||
                                                 l_v_contract_office_req || '%''';
      END IF;
      IF (TRIM(p_t_parametre.contractNumber) IS NOT NULL)
      THEN
        l_t_whereclause.EXTEND;
        l_t_whereclause(l_t_whereclause.last) := 'CMX_ORS.c_contract.numero_contrat = ''' ||
                                                 p_t_parametre.contractNumber || '''';
      END IF;
    
      -- si p_v_req contient déjà un select 
      -- alors on gère une intersection entre les requètes
      IF (p_v_req IS NOT NULL)
      THEN
        p_v_req := p_v_req || chr(13) || 'INTERSECT' || chr(13);
      END IF;
    
      p_v_req := p_v_req || 'SELECT CMX_ORS.c_party_contract_rel.PARTY_ID AS rowid_object';
      p_v_req := p_v_req || chr(13) || 'FROM CMX_ORS.c_contract, CMX_ORS.c_party_contract_rel';
      p_v_req := p_v_req || chr(13) ||
                 'where CMX_ORS.c_contract.ROWID_OBJECT = CMX_ORS.c_party_contract_rel.contract_id';
      p_v_req := p_v_req || chr(13) || 'and ' || PKG_SEARCHPARTY.FCT_WHERECLAUSE(l_t_whereclause);
    
    END IF;
  END;

  /**
  *    PRC_SEARCH_IN_C_PARTY permet de construire une requette pour
  *    préfiltrer les résultats en utilisants
  *    les critères de recherche sur les individus
  */
  PROCEDURE PRC_SEARCH_IN_C_PARTY(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB) IS
    l_v_last_name_search_req  CMX_ORS.C_PARTY.LAST_NAME_SEARCH%TYPE := PKG_SEARCHPARTY.FCT_UPPER_TRANSLATE(p_t_parametre.lastName);
    l_v_first_name_search_req CMX_ORS.C_PARTY.FIRST_NAME_SEARCH%TYPE := PKG_SEARCHPARTY.FCT_UPPER_TRANSLATE(p_t_parametre.firstName);
    l_t_whereclause           PKG_SEARCHPARTY.T_whereclause;
  BEGIN
    l_t_whereclause := PKG_SEARCHPARTY.T_whereclause(' ');
  
    -- si un critère contrat est passé en paramètre
    IF (TRIM(p_t_parametre.lastName) IS NOT NULL OR TRIM(p_t_parametre.firstName) IS NOT NULL OR
       TRIM(p_t_parametre.gender) IS NOT NULL OR TRIM(p_t_parametre.partyType) IS NOT NULL OR
       TRIM(p_t_parametre.legalForm) IS NOT NULL OR TRIM(p_t_parametre.refClient) IS NOT NULL OR
       TRIM(p_t_parametre.mdmId) IS NOT NULL)
    THEN
      l_t_whereclause.DELETE;
    
      -- CI-dessous, on aliment le tableau l_t_whereclause permettant de 
      -- générer la clause where pour filtrer les adresses
      IF (TRIM(p_t_parametre.lastName) IS NOT NULL)
      THEN
        l_t_whereclause.EXTEND;
        l_t_whereclause(l_t_whereclause.last) := 'CMX_ORS.C_PARTY.last_name_search LIKE ''' ||
                                                 l_v_last_name_search_req || '%''';
      END IF;
      IF (TRIM(p_t_parametre.firstName) IS NOT NULL)
      THEN
        l_t_whereclause.EXTEND;
        l_t_whereclause(l_t_whereclause.last) := 'CMX_ORS.C_PARTY.first_Name_search LIKE ''' ||
                                                 l_v_first_name_search_req || '%''';
      END IF;
      IF (TRIM(p_t_parametre.partyType) IS NOT NULL)
      THEN
        l_t_whereclause.EXTEND;
        l_t_whereclause(l_t_whereclause.last) := 'CMX_ORS.C_PARTY.GENDER_CD = ''' ||
                                                 p_t_parametre.gender || '''';
      END IF;
      IF (TRIM(p_t_parametre.gender) IS NOT NULL)
      THEN
        l_t_whereclause.EXTEND;
        l_t_whereclause(l_t_whereclause.last) := 'CMX_ORS.C_PARTY.PARTY_TYPE = ''' ||
                                                 p_t_parametre.partyType || '''';
      END IF;
      IF (TRIM(p_t_parametre.legalForm) IS NOT NULL)
      THEN
        l_t_whereclause.EXTEND;
        l_t_whereclause(l_t_whereclause.last) := 'CMX_ORS.C_PARTY.LEGAL_FORM_CD = ''' ||
                                                 p_t_parametre.legalForm || '''';
      END IF;
      IF (TRIM(p_t_parametre.refClient) IS NOT NULL)
      THEN
        l_t_whereclause.EXTEND;
        l_t_whereclause(l_t_whereclause.last) := 'CMX_ORS.C_PARTY.DEBRANCHEMENT_SIGEO = ''' ||
                                                 p_t_parametre.refClient || '''';
      END IF;
      IF (TRIM(p_t_parametre.mdmId) IS NOT NULL)
      THEN
        l_t_whereclause.EXTEND;
        l_t_whereclause(l_t_whereclause.last) := 'CMX_ORS.C_PARTY.ROWID_OBJECT = ''' ||
                                                 p_t_parametre.mdmId || '''';
      END IF;
    
      -- on génére un select permettant de récupérer tout les 
      -- C_PARTY.ROWID_OBJECT répondant aux critères de recherche contrat
      p_v_req := p_v_req || 'SELECT CMX_ORS.c_party.rowid_object AS rowid_object';
      p_v_req := p_v_req || chr(13) || '  FROM CMX_ORS.c_party';
      p_v_req := p_v_req || chr(13) || ' WHERE ' ||
                 PKG_SEARCHPARTY.FCT_WHERECLAUSE(l_t_whereclause);
    
    END IF;
  END;

  /**
  *    PRC_SEARCH_IN_C_PARTY_ELECT_AD permet de construire une requette pour
  *    préfiltrer les résultats en utilisants
  *    les critères de recherche sur l'adresse email
  */
  PROCEDURE PRC_SEARCH_IN_C_PARTY_ELECT_AD(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB) IS
  BEGIN
    -- si p_v_req contient déjà un select 
    -- alors on gère une intersection entre les requètes
    IF (p_v_req IS NOT NULL AND TRIM(p_t_parametre.electronicAddress) IS NOT NULL)
    THEN
      p_v_req := p_v_req || chr(13) || 'INTERSECT' || chr(13);
    END IF;
  
    IF (TRIM(p_t_parametre.electronicAddress) IS NOT NULL)
    THEN
      p_v_req := p_v_req || 'SELECT PARTY_ID AS rowid_object';
      p_v_req := p_v_req || chr(13) || '  FROM CMX_ORS.C_PARTY_ELECT_AD';
      p_v_req := p_v_req || chr(13) || ' WHERE electronic_address LIKE''' ||
                 p_t_parametre.electronicAddress || '%''';
    END IF;
  END;

  /**
  *    PRC_SEARCH_IN_C_PARTY_PHONE permet de construire une requette pour
  *    préfiltrer les résultats en utilisants
  *    les critères de recherche sur le numéro de telephone (dom, fax, bur, por)
  */
  PROCEDURE PRC_SEARCH_IN_C_PARTY_PHONE(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB) IS
  BEGIN
    -- si p_v_req contient déjà un select 
    -- alors on gère une intersection entre les requètes
    IF (p_v_req IS NOT NULL AND TRIM(p_t_parametre.phoneNumber) IS NOT NULL)
    THEN
      p_v_req := p_v_req || chr(13) || 'INTERSECT' || chr(13);
    END IF;
  
    IF (TRIM(p_t_parametre.phoneNumber) IS NOT NULL)
    THEN
      p_v_req := p_v_req || 'SELECT PARTY_ID AS rowid_object';
      p_v_req := p_v_req || chr(13) || '  FROM CMX_ORS.c_party_PHONE';
      p_v_req := p_v_req || chr(13) || ' WHERE PHONE_NUMBER = ''' || p_t_parametre.phoneNumber || '''';
    END IF;
  END;

  /**
  *    PRC_SEARCH_IN_C_PARTY_ELECT_AD permet de construire une requette pour
  *    préfiltrer les résultats en utilisants
  *    les critères de recherche sur l'adresse email
  */
  PROCEDURE PRC_SEARCH_IN_C_PARTYXREF(p_t_parametre IN T_SEARCHPARTY_IN, p_v_req IN OUT CLOB) IS
  BEGIN
    -- si p_v_req contient déjà un select 
    -- alors on gère une intersection entre les requètes
    IF (p_v_req IS NOT NULL AND TRIM(p_t_parametre.crmId) IS NOT NULL)
    THEN
      p_v_req := p_v_req || chr(13) || 'INTERSECT' || chr(13);
    END IF;
  
    IF (TRIM(p_t_parametre.crmId) IS NOT NULL)
    THEN
      p_v_req := p_v_req || 'SELECT C_PARTY_XREF.ROWID_OBJECT';
      p_v_req := p_v_req || chr(13) || '  FROM CMX_ORS.C_PARTY_XREF, CMX_ORS.C_REPOS_SYSTEM';
      p_v_req := p_v_req || chr(13) ||
                 ' WHERE C_PARTY_XREF.ROWID_SYSTEM = C_REPOS_SYSTEM.ROWID_SYSTEM';
      p_v_req := p_v_req || chr(13) || ' AND C_REPOS_SYSTEM.SYSTEM_NAME = ''CRM''';
      p_v_req := p_v_req || chr(13) || ' AND C_PARTY_XREF.PKEY_SRC_OBJECT = ''' ||
                 p_t_parametre.crmId || '''';
    END IF;
  END;

  /**
  *    FCT_UPPER_TRANSLATE permet de remplacer les caractères accentuée et de mettre en majuscule
  */
  FUNCTION FCT_UPPER_TRANSLATE(p_v_TEXT IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN UPPER(TRANSLATE(LOWER(p_v_TEXT), 'àäéèêëîïôöùüç', 'aaeeeeiioouuc'));
  
  EXCEPTION
    WHEN OTHERS THEN
      RETURN '';
  END;

  /**
  *    FCT_WHERECLAUSE permet de retourner la where clause pour un select à partir d'un tableau passé en parametre
  *    ATTENTION: le mot clé 'WHERE' n'est pas présent dans le résultat
  *    tous les critères sont concaténés avec la clause AND
  */
  FUNCTION FCT_WHERECLAUSE(p_t_whereclause IN T_whereclause) RETURN CLOB IS
    l_v_retour VARCHAR2(1000);
  BEGIN
    l_v_retour := p_t_whereclause(1);
    FOR i IN p_t_whereclause.first + 1 .. p_t_whereclause.last
    LOOP
      l_v_retour := l_v_retour || chr(13) || ' AND ' || p_t_whereclause(i);
    END LOOP;
    RETURN l_v_retour;
  END;
END PKG_SEARCHPARTY;
/
